MySQL is one of the best RDBMS being used for developing web-based software applications and in this article we will cover about using nodejs for CRUD operations with MySQL database with a step by step approach with code examples

• Database - A database is a collection of tables, with related data.
• Table - A table is a matrix with data. A table in a database looks like a simple spreadsheet.
• Primary Key - A primary key is unique. A key value can not occur twice in one table. With a key, you can find at most one row.
• Foreign Key - A foreign key is the linking pin between two tables.

Nodejs

Nodejs is a platform built on Chrome’s JavaScript runtime for easily building fast and scalable network applications.

Steps:

1) To install MySQL in nodejs use npm package:

    npm install mysql

2) Link MySQL with Nodejs server

var mysql = require('mysql');

3) To create a new MySQL database and table - Create database: Creating separate space in MySQL

    Syntax:
        create database <DB_Name>;

• Use database: To use the created Database

  Syntax:
      use <DB_Name>;
  

• Create Table: Creating table in Database

  Syntax:
  
      create table <TableName>(Column1 Datatype,Column2 Datatype,...);
  

• Connect MySQL and Nodejs:

  MySQL configuration

  Example :

  var connection = mysql.createConnection({ // creating connection string
      host     : 'localhost', //host name
      user     : 'root', // mySQL username  
      password : '', // mySql Password
      database : 'db_name' // database Name
  });
  

• connecting with nodejs

  Example :

  connection.connect(function(err){ 
      if(!err) {
          console.log("Database is connected ... ");   
      } else {
          console.log("Error connecting database ... ");   
      }
  });
  

• Insert table: Insert data in created table

  Syntax:

  insert into <TableName> values('string_data',integer_data,....);
  

  Example:

  connection.query("insert into user values("+value1+",'"+value2+"',"+value2+",'" value4+'")",
   function(err, rows, fields) { // INSERT query in nodejs file
     connection.end();
     if (!err)
      console.log('The solution is: ', rows);
     else
      console.log('Error while performing Query.');
    });
  

• Select table: To retrieve data from table

  i) Syntax for access all Rows :

  select * from <TableName>; (or) select <column_name> from <TableName>;
  

  ii) Syntax for access particular Rows :

  select * from <TableName> where <column_name>='<user_data'>;
  

  Example :

  var query="select * from db_name where column1='"+value1+"' and column2='"+value2+"'";
  connection.query(query, function(err, rows, fields) { // SELECT query in nodejs file
  connection.end();
      if (!err){
          console.log('The solution is: ', rows);
      } else { 
          console.log('Error while performing Query.');
      }
  });
  

• Update table: Syntax for update row:

  update <TableName> set <update_columnName> = <update_value> where <column_name>='<user_data'>; 
  

  Example:

  var query="update db_name set updatecolumn='"+updatevalue+"' 
  where conditioncolumn='"+conditionvalue+"'";
      connection.query(query, function(err, rows, fields) {           // SELECT query in nodejs file
      connection.end();
        if (!err)
          console.log('The solution is: ', rows);
        else
          console.log('Error while performing Query.');
        });
  

• Delete table: To delete data or table

  i) Syntax for delete all data:

      delete table <TableName>;
  

  ii) Syntax for delete particular data:

      delete table <TableName> where <columnName>="<userdata>";
  

  Example:

  var query="delete table db_name where column1='"+value1;
  connection.query(query, function(err, rows, fields) { // SELECT query in nodejs file
      connection.end();
        if (!err){
          console.log('The solution is: ', rows);
        }else{
          console.log('Error while performing Query.');
        }
  });
  

MySQL aggregate functions:

The AVG function calculates the average value of a set of values. It ignores NULL values in the calculation.

Syntax:
    select AVG(<ColumnName) from <TableName>;

The COUNT function returns the number of the rows in a table.

Syntax:
    select COUNT(*) from <TableName> (or) select COUNT(<columnName>) from <TableName>;

The SUM function returns the sum of a set of values.

Syntax:
    select SUM(<ColumnName>) from <TableName>;

The MIN function returns the minimum value in a set of values.

Syntax:
    select MIN(<ColumnName>) from <TableName>;

The MAX function returns the maximum value in a set of values.

Syntax:
    select MAX(<ColumnName>) from <TableName>;

For any query contact Clofus Innovations.